package com.example.gfile.service;

import com.example.gfile.entity.Db;
import com.example.gfile.exception.GFileException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class SqlMapperService {

    @Autowired
    private DbService dbService;

    public List<List<Map<String, Object>>> executeQuerySql(String sql, String id) {
        if (StringUtils.isEmpty(sql)) {
            return new ArrayList<>();
        }
        log.info("执行查询sql:{}", sql);
        PreparedStatement pst = null;
        Connection sqlSession = getSqlSession(id);
        try {
            pst = sqlSession.prepareStatement(sql);
            ResultSet result = pst.executeQuery();
            return parseResult(result);
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new GFileException(e.getMessage(), e);
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                    sqlSession.close();
                } catch (SQLException e) {
                    throw new GFileException(e.getMessage(), e);
                }
            }
        }
    }


    private List<List<Map<String, Object>>> parseResult(ResultSet result) throws SQLException {
        List<List<Map<String, Object>>> resultList = new ArrayList<>();
        ResultSetMetaData md = result.getMetaData(); //获得结果集结构信息,元数据
        int columnCount = md.getColumnCount();   //获得列数
        while (result.next()) {
            List<Map<String, Object>> rowList = new ArrayList<>();
            Map<String, Object> rowData = new HashMap<String, Object>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), result.getObject(i));
            }
            rowList.add(rowData);
            resultList.add(rowList);
        }
        return resultList;
    }


    /**
     * 获取Session
     */
    public Connection getSqlSession(String id) {
        try {
            Db db = dbService.selectById(id);
            String url = db.getUrl();
            String userName = db.getUserName();
            String password = db.getPassword();
            String driverClassName = db.getDriverClassName();
            Class.forName(driverClassName);
            return DriverManager.getConnection(url, userName, password);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new GFileException("数据库初始化失败", e);
        }
    }

}
